<?php

class AvisosModel extends ModelBase
{

	public function getAvisoTramite()
	{
		include("config.php");
		
		$cant = 0;
		
		$select = " COUNT(*) cant ";
		$from = " ga_tramite t, ga_tramite_estado te ";
	  	$where = " t.activo = 'S'
				and t.id = te.id_tramite 
				and te.actual = 'S' 
				and te.aviso <= DATEDIFF(CURRENT_DATE() , te.fecha_cambio_estado)
				and te.duracion >= DATEDIFF(CURRENT_DATE() , te.fecha_cambio_estado) ";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
	
    	$sqlpersonal->load();
			
		$datoTmp = &$sqlpersonal->items[$sqlpersonal->get_count()-1];

		$cant = $datoTmp->get_data("cant");
		
    	return $cant;
	}	
	
	public function getDuracionTramite()
	{
		include("config.php");
		
		$cant = 0;
		
		$select = " COUNT(*) cant ";
		$from = " ga_tramite t, ga_tramite_estado te ";
	  	$where = " t.activo = 'S'
				and t.id = te.id_tramite 
				and te.actual = 'S' 
				and te.duracion <= DATEDIFF(CURRENT_DATE() , te.fecha_cambio_estado)";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
		
		$sqlpersonal->load();
	
    	$datoTmp = &$sqlpersonal->items[$sqlpersonal->get_count()-1];

		$cant = $datoTmp->get_data("cant");
		
    	return $cant;
	}	
	
	public function getSolicitudLegajoTramite()
	{
		include("config.php");
		
		$cant = 0;
		
		$select = " COUNT(*) cant ";
		$from = " ga_tramite t, ga_tramite_estado te ";
	  	$where = " t.activo = 'S'
				and t.id = te.id_tramite 
				and te.actual = 'S' 
				and te.solicitud_legajo <= DATEDIFF(CURRENT_DATE() , te.fecha_cambio_estado)
				and te.solicitud_legajo <> 0 
				and te.duracion >= DATEDIFF(CURRENT_DATE() , te.fecha_cambio_estado) ";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
		
		$sqlpersonal->load();
	
    	$datoTmp = &$sqlpersonal->items[$sqlpersonal->get_count()-1];

		$cant = $datoTmp->get_data("cant");
		
    	return $cant;
	}	
	
	public function getRespaldoActual()
	{
		include("config.php");
		
		$dif = 0;
		
		$select = " DATEDIFF( CURRENT_DATE() , r.fecha) dif ";
		$from = " ga_respaldo r ";
	  	$where = " r.activo = 'S' ";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
	
    	$sqlpersonal->load();
		
		
		$datoTmp = &$sqlpersonal->items[$sqlpersonal->get_count()-1];

		$dif = $datoTmp->get_data("dif");
		
    	return $dif;
	}	


	public function getParametrizacion()
	{
		include("config.php");
		
		$cant = 0;

		$select = " count(*) cant ";
		$from = " ga_estado_tramite e ";
	  	$where = " e.activo = 'S' ";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
	
    	$sqlpersonal->load();
		
		
		$datoTmp = &$sqlpersonal->items[$sqlpersonal->get_count()-1];
	
		
		if($datoTmp->get_data("cant") == 0)
		{
			$cant = 1;
		}
		
		
		$select = " t.id id ";
		$from = " ga_tipo_tramite t ";
	  	$where = " t.activo = 'S' ";	
				
		$sqlperson = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlperson->set_select($select); 
	  	$sqlperson->set_from($from);
		$sqlperson->set_where($where);
	
    	$sqlperson->load();
		
		for($j=0; $j<$sqlperson->get_count(); $j++) 
		{
			$datoTmp = &$sqlperson->items[$j];
			
			$select = " count(*) cant ";
			$from = " ga_estado_tramite e";
			$where = " e.activo = 'S' and e.id not in (select id_estado_tramite from ga_tipo_tramite_estado tt where tt.id_tipo_tramite = ".$datoTmp->get_data("id").")";	
				
			$sqlpers = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
			$sqlpers->set_select($select); 
	  		$sqlpers->set_from($from);
			$sqlpers->set_where($where);
	
    		$sqlpers->load();
			
			$datoTmpAux = &$sqlpers->items[$sqlpers->get_count()-1];
			
			$cant = $cant + $datoTmpAux->get_data("cant");
		}
		
		//
		
		//$cant = $datoTmp->get_data("cant");
		
    	return $cant;
	}
	
	
	public function getCantParamEstados()
	{
		include("config.php");
		
		$cant = 0;

		$select = " count(*) cant ";
		$from = " ga_estado_tramite e ";
	  	$where = " e.activo = 'S' ";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
	
    	$sqlpersonal->load();
		
		
		$datoTmp = &$sqlpersonal->items[$sqlpersonal->get_count()-1];

		$cant = $datoTmp->get_data("cant");
		
    	return $cant;
	}
	
	public function getParamTipEstados()
	{
		include("config.php");
		$result = array();
		
		$select = " t.id id, t.descripcion descripcion ";
		$from = " ga_tipo_tramite t ";
	  	$where = " t.activo = 'S' ";	
				
		$sqlpersonal = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
		$sqlpersonal->set_select($select); 
	  	$sqlpersonal->set_from($from);
		$sqlpersonal->set_where($where);
	
    	$sqlpersonal->load();
		
		for($j=0; $j<$sqlpersonal->get_count(); $j++) 
		{
			$datoTmp = &$sqlpersonal->items[$j];
			
			$select = " count(*) cant ";
			$from = " ga_estado_tramite e ";
			$where = " e.activo = 'S' and id not in (select id_estado_tramite from ga_tipo_tramite_estado tt where tt.id_tipo_tramite = ".$datoTmp->get_data("id").")";	
				
			$sqlpers = new SqlPersonalizado($config->get('dbhost'), $config->get('dbuser'), $config->get('dbpass') );
			$sqlpers->set_select($select); 
	  		$sqlpers->set_from($from);
			$sqlpers->set_where($where);
	
    		$sqlpers->load();
			
			$datoTmpAux = &$sqlpers->items[$sqlpers->get_count()-1];
			
			if($datoTmpAux->get_data("cant") > 0)
			{
				$result[] = $datoTmp->get_data("descripcion");
			}
			
		}
    	return $result;
	}
	
	public function habilita_bandera()
	{
		$resp = "N";
		
		$cant_a = $this->getAvisoTramite();
		$cant_d = $this->getDuracionTramite();
		$cant_s = $this->getSolicitudLegajoTramite();
		$dif_r = $this->getRespaldoActual();
		$cant_p = $this->getParametrizacion();
		
		
		
		if($cant_a > 0 || $cant_d > 0 || $cant_s > 0 || $dif_r > 20 || $cant_p > 0)
		{
			$resp = "S";
		}
		
		return $resp;
	}
	
	public function habilita_bandera_actualiza()
	{
		$resp = "S";
		
		$ftp_server = "ftp.bch-soluciones.com";
		$ftp_user_name = "bchsoluc"; 
		$ftp_user_pass = "lr4y19C9bX";
		
		$local_file = './ajax/existe_actualiza.php';
		$server_file = '/public_html/demo/gautomotor/actualiza/existe_actualiza.php';

		$conn_id = ftp_connect($ftp_server);

		$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

		if (ftp_get($conn_id, $local_file, $server_file, FTP_BINARY)) 
		{
		    $resp =  "S";
		} 
		else 
		{
    		$resp = "Ha habido un problema\n";
		}

		ftp_close($conn_id);
		
		if($resp == "S")
		{
			$archivoE = './ajax/existe_actualiza.php';
			//$archivoS = "activacion/index".$dato->get_data("id_usuario").".html";

			$actual = file_get_contents($archivoE);

			$result = strpos($actual, "PENDIENTE");
			if($result == false)
			{
				$resp = "No hay actualizacion pendiente";
			}

			return $resp;
		}
	}
}
//actualiza00003
?>